unit StockDB;

interface

uses
  Windows, SysUtils, Dialogs, Classes, DB, ADODB, ShellAPI, Variants, Controls,ComCtrls,
  StdCtrls, Forms, ACTIVEX ;

type
  TStockDBOper = class
  private
    MySQLConnect: TADOConnection;
    DataQuery: TADOQuery;
    UserName,Password,DataBaseName,ImportName: String;
    function WinExecAndWait32(FileName: String; Visibility: integer): integer;
    procedure RunSQL(ASQL: String);
    procedure InsertStockInfo(ACode,ATableName: String);
    procedure Init();
  public
    constructor Create(AUserName,APassword,ADataBaseName,AImportName: String); overload;
    constructor Create(AImportName: String);overload;
    constructor Create(); overload;
    destructor Destroy(); override;
    procedure CreateImpStockDataTable();
    procedure DropImpStockDataTable();
    procedure EmptyStockDataTable(ATableName: String);
    procedure EmptyStockInfoTalbe();
    procedure CreateStockDataTable(ATableName: String);
    procedure CreateStockInfoTable();
    procedure ImportCSV(AStockCode,ACSVPath,ATableName: String);
  end;

const
  CREATE_IMP_TABLE_SQL = 'CREATE TABLE IF NOT EXISTS %s (DATE DATE, OPEN DOUBLE(10,2) , High DOUBLE(10,2) , Low DOUBLE(10,2), Close DOUBLE(10,2), Volume BIGINT UNSIGNED, AdjClose DOUBLE(10,2))';
  DROP_TABLE_SQL ='DROP TABLE IF EXISTS %s';
  EMPTY_TABLE_SQL ='DELETE FROM %s';

  CREATE_STOCK_DATA_TABLE_SQL = 'CREATE TABLE IF NOT EXISTS %s (ID INT auto_increment primary key,DATE DATE, OPEN DOUBLE(10,2) , High DOUBLE(10,2) , Low DOUBLE(10,2), Close DOUBLE(10,2), Volume BIGINT UNSIGNED, AdjClose DOUBLE(10,2), CODE VARCHAR(10))';
  CREATE_STOCK_INFO = 'CREATE TABLE IF NOT EXISTS STOCK_INFO (CODE VARCHAR(10) PRIMARY KEY ,NAME VARCHAR(10),TABLE_NAME VARCHAR(20),MAXVOLUME BIGINT UNSIGNED, MINVOLUME BIGINT UNSIGNED, AVGVOLUME BIGINT UNSIGNED, TOTALVOLUME BIGINT UNSIGNED)';
  //STOCK_DATA_TABLE_NAME = 'STOCK_DATA';
  STOCK_INFO_TABLE_NAME = 'STOCK_INFO';

  CONNECT_STR ='DRIVER={MySQL ODBC 3.51 Driver}; SERVER=127.0.0.1; PORT=3306; DATABASE=%s; USER=%s; PASSWORD=%s; OPTION=3';

  IMPORT_COMMAND = 'mysqlimport --delete --fields-optionally-enclosed-by="" --fields-terminated-by="," --lines-terminated-by="\r\n"  -u%s -p%s %s "%s"';

implementation

{ TStockDBOper }

constructor TStockDBOper.Create(AUserName,APassword,ADataBaseName,AImportName: String);
begin


  UserName := AUserName;
  Password := APassword;
  DataBaseName:= ADataBaseName;
  ImportName := AImportName;
  Init();
end;

constructor TStockDBOper.Create(AImportName: String);
begin
  self.Create();
  ImportName := AImportName;
end;

constructor TStockDBOper.Create;
begin
  //TODO 1: read iniFile
  Init();
end;

procedure TStockDBOper.CreateImpStockDataTable();
begin
  RunSQL(Format(CREATE_IMP_TABLE_SQL,[ImportName]));
end;

procedure TStockDBOper.CreateStockDataTable(ATableName: String);
begin
  RunSQL(Format(CREATE_STOCK_DATA_TABLE_SQL,[ATableName]));
end;

procedure TStockDBOper.CreateStockInfoTable;
begin
  RunSQL(CREATE_STOCK_INFO);
end;

destructor TStockDBOper.Destroy;
begin
  DataQuery.Close;
  DataQuery.Free;
  MySQLConnect.Close;
  MySQLConnect.Free;
  inherited;
end;

procedure TStockDBOper.DropImpStockDataTable();
begin
  RunSQL(Format(DROP_TABLE_SQL,[ImportName]));
end;

procedure TStockDBOper.EmptyStockDataTable(ATableName: String);
begin
   RunSQL(Format(EMPTY_TABLE_SQL,[ATableName]));
end;

procedure TStockDBOper.EmptyStockInfoTalbe;
begin
  RunSQL(Format(EMPTY_TABLE_SQL,[STOCK_INFO_TABLE_NAME]));
end;

procedure TStockDBOper.ImportCSV(AStockCode, ACSVPath, ATableName: String);
var
  ImportCommand: TStringList;
begin
  ImportCommand := TStringList.Create;
  try
    ImportCommand.Add(Format(IMPORT_COMMAND,[UserName,Password,DataBaseName,ACSVPath + ImportName +'.csv']));
    ImportCommand.SaveToFile(ExtractFilePath(Application.ExeName) + ImportName + '.bat');
  finally
    ImportCommand.Free;
  end;
  WinExecAndWait32(ExtractFilePath(Application.ExeName) + ImportName + '.bat',SW_HIDE);
  RunSQL('INSERT INTO ' + ATableName +
    '(DATE,OPEN ,High,Low,Close , Volume , AdjClose , CODE) SELECT DATE, OPEN  , High , Low , Close , Volume , AdjClose , ''' + AStockCode + ''' FROM ' + ImportName);
  InsertStockInfo(AStockCode,ATableName);
end;

procedure TStockDBOper.Init;
begin
  DataQuery := TADOQuery.Create(nil);
  MySQLConnect := TADOConnection.Create(nil);
  MySQLConnect.LoginPrompt := false;
  DataQuery.Connection := MySQLConnect;
  MySQLConnect.ConnectionString := Format(CONNECT_STR,[DataBaseName,UserName,Password]);
  MySQLConnect.Open();
end;

procedure TStockDBOper.InsertStockInfo(ACode,ATableName: String);
begin
  //RUNSQL('DELETE FROM ' + STOCK_INFO_TABLE_NAME + ' WHERE CODE = ''' + ACode + '''');
  RunSQL('INSERT INTO ' + STOCK_INFO_TABLE_NAME + '(CODE,TABLE_NAME) VALUES(''' + ACode + ''','''+ ATableName + ''')');
end;

procedure TStockDBOper.RunSQL(ASQL: String);
begin
  DataQuery.Close;
  DataQuery.SQL.Text := ASQL;
  DataQuery.ExecSQL;
end;

function TStockDBOper.WinExecAndWait32(FileName:String; Visibility : integer):integer;
var
 zAppName:array[0..512] of char;
 zCurDir:array[0..255] of char;
 WorkDir:String;
 StartupInfo:TStartupInfo;
 ProcessInfo:TProcessInformation;
 CResult: Cardinal;
begin
 StrPCopy(zAppName,FileName);
 GetDir(0,WorkDir);
 StrPCopy(zCurDir,WorkDir);
 FillChar(StartupInfo,Sizeof(StartupInfo),#0);
 StartupInfo.cb := Sizeof(StartupInfo);

 StartupInfo.dwFlags := STARTF_USESHOWWINDOW;
 StartupInfo.wShowWindow := Visibility;
 if not CreateProcess(nil,
   zAppName,                      { pointer to command line string }
   nil,                           { pointer to process security attributes }
   nil,                           { pointer to thread security attributes }
   false,                         { handle inheritance flag }
   CREATE_NEW_CONSOLE or          { creation flags }
   NORMAL_PRIORITY_CLASS,
   nil,                           { pointer to new environment block }
   nil,                           { pointer to current directory name }
   StartupInfo,                   { pointer to STARTUPINFO }
   ProcessInfo) then Result := -1 { pointer to PROCESS_INF }

 else begin
   WaitforSingleObject(ProcessInfo.hProcess,INFINITE);
   GetExitCodeProcess(ProcessInfo.hProcess,CResult);
   result := CResult;
 end;
end;

initialization
  coinitialize(nil);
finalization
  counInitialize;
end.

